1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmBillingRecord1
6
7 Public Sub Getdata()
8 Try
9 con = New SqlConnection(cs)
10 con.Open()
11 cmd = New SqlCommand("Select Inv_ID, RTRIM(InvoiceNo), InvoiceDate, ServiceID,RTRIM(ServiceCode),RTRIM(Customer.CustomerID),RTRIM(Name), RepairCharges, Upfront, ProductCharges, ServiceTaxPer, ServiceTax, GrandTotal, TotalPaid, Balance, RTRIM(InvoiceInfo1.Remarks) from Customer,Service,InvoiceInfo1 where Customer.ID=Service.CustomerID and Service.S_ID=InvoiceInfo1.ServiceID order by InvoiceDate", con)
12 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
13 dgw.Rows.Clear()
14 While (rdr.Read() = True)
15 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13), rdr(14), rdr(15))
16 End While
17 con.Close()
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
23 Getdata()
24 fillInvoiceNo()
25 End Sub
26
27 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
28 Try
29 If dgw.Rows.Count > 0 Then
30 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
31 If lblSet.Text = "Billing" Then
32 frmBilling1.Show()
33 Me.Hide()
34 frmBilling1.txtID.Text = dr.Cells(0).Value.ToString()
35 frmBilling1.txtInvoiceNo.Text = dr.Cells(1).Value.ToString()
36 frmBilling1.dtpInvoiceDate.Text = dr.Cells(2).Value.ToString()
37 frmBilling1.txtS_ID.Text = dr.Cells(3).Value.ToString()
38 frmBilling1.txtServiceCode.Text = dr.Cells(4).Value.ToString()
39 frmBilling1.txtCustomerID.Text = dr.Cells(5).Value.ToString()
40 frmBilling1.txtCustomerName.Text = dr.Cells(6).Value.ToString()
41 frmBilling1.txtRepairCharges.Text = dr.Cells(7).Value.ToString()
42 frmBilling1.txtUpfront.Text = dr.Cells(8).Value.ToString()
43 frmBilling1.txtProductCharges.Text = dr.Cells(9).Value.ToString()
44 frmBilling1.txtServiceTaxPer.Text = dr.Cells(10).Value.ToString()
45 frmBilling1.txtServiceTaxAmount.Text = dr.Cells(11).Value.ToString()
46 frmBilling1.txtGrandTotal.Text = dr.Cells(12).Value.ToString()
47 frmBilling1.txtTotalPayment.Text = dr.Cells(13).Value.ToString()
48 frmBilling1.txtPaymentDue.Text = dr.Cells(14).Value.ToString()
49 frmBilling1.txtRemarks.Text = dr.Cells(15).Value.ToString()
50 frmBilling1.btnSave.Enabled = False
51 frmBilling1.btnUpdate.Enabled = True
52 frmBilling1.btnPrint.Enabled = True
53 frmBilling1.btnDelete.Enabled = True
54 frmBilling1.lblSet.Text = "Not Allowed"
55 frmBilling1.btnAdd.Enabled = False
56 con = New SqlConnection(cs)
57 con.Open()
58 Dim sql As String = "SELECT RTRIM(ProductCode),RTRIM(ProductName), Invoice1_Product.CostPrice, Invoice1_Product.SellingPrice, Invoice1_Product.Margin, Invoice1_Product.Qty, Invoice1_Product.Amount, Invoice1_Product.DiscountPer, Invoice1_Product.Discount, Invoice1_Product.VATPer, Invoice1_Product.VAT, Invoice1_Product.TotalAmount,Product.PID from InvoiceInfo1,Invoice1_Product,Product where InvoiceInfo1.Inv_ID=Invoice1_Product.InvoiceID and Product.PID=Invoice1_Product.ProductID and InvoiceInfo1.Inv_ID=@d1 and Qty <> 0"
59 cmd = New SqlCommand(sql, con)
60 cmd.Parameters.AddWithValue("@d1", dr.Cells(0).Value.ToString())
61 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
62 frmBilling1.DataGridView1.Rows.Clear()
63 While (rdr.Read() = True)
64 frmBilling1.DataGridView1.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12))
65 End While
66 con.Close()
67 con = New SqlConnection(cs)
68 con.Open()
69 Dim sql1 As String = "SELECT RTRIM(PaymentMode),Invoice1_Payment.TotalPaid,PaymentDate from InvoiceInfo1,Invoice1_Payment where InvoiceInfo1.Inv_ID=Invoice1_Payment.InvoiceID and InvoiceInfo1.Inv_ID=@d1"
70 cmd = New SqlCommand(sql1, con)
71 cmd.Parameters.AddWithValue("@d1", dr.Cells(0).Value.ToString())
72 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
73 frmBilling1.DataGridView2.Rows.Clear()
74 While (rdr.Read() = True)
75 frmBilling1.DataGridView2.Rows.Add(rdr(0), rdr(1), rdr(2))
76 End While
77 con.Close()
78 lblSet.Text = ""
79 End If
80 End If
81 Catch ex As Exception
82 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
83 End Try
84 End Sub
85
86 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
87 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
88 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
89 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
90 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
91 End If
92 Dim b As Brush = SystemBrushes.ControlText
93 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
94
95 End Sub
96 Sub fillInvoiceNo()
97 Try
98 con = New SqlConnection(cs)
99 con.Open()
100 adp = New SqlDataAdapter()
101 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(InvoiceNo) FROM InvoiceInfo1", con)
102 ds = New DataSet("ds")
103 adp.Fill(ds)
104 dtable = ds.Tables(0)
105 cmbInvoiceNo.Items.Clear()
106 For Each drow As DataRow In dtable.Rows
107 cmbInvoiceNo.Items.Add(drow(0).ToString())
108 Next
109 Catch ex As Exception
110 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
111 End Try
112 End Sub
113 Sub Reset()
114 cmbInvoiceNo.Text = ""
115 txtCustomerName.Text = ""
116 fillInvoiceNo()
117 dtpDateFrom.Text = Today
118 dtpDateTo.Text = Today
119 DateTimePicker2.Text = Today
120 DateTimePicker1.Text = Today
121 Getdata()
122 End Sub
123 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
124 Reset()
125 End Sub
126
127 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
128 Me.Close()
129 End Sub
130
131
132 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
133 Dim rowsTotal, colsTotal As Short
134 Dim I, j, iC As Short
135 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
136 Dim xlApp As New Excel.Application
137 Try
138 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
139 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
140 xlApp.Visible = True
141
142 rowsTotal = dgw.RowCount
143 colsTotal = dgw.Columns.Count - 1
144 With excelWorksheet
145 .Cells.Select()
146 .Cells.Delete()
147 For iC = 0 To colsTotal
148 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
149 Next
150 For I = 0 To rowsTotal - 1
151 For j = 0 To colsTotal
152 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
153 Next j
154 Next I
155 .Rows("1:1").Font.FontStyle = "Bold"
156 .Rows("1:1").Font.Size = 12
157
158 .Cells.Columns.AutoFit()
159 .Cells.Select()
160 .Cells.EntireColumn.AutoFit()
161 .Cells(1, 1).Select()
162 End With
163 Catch ex As Exception
164 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
165 Finally
166 'RELEASE ALLOACTED RESOURCES
167 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
168 xlApp = Nothing
169 End Try
170 End Sub
171
172 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
173 Try
174 con = New SqlConnection(cs)
175 con.Open()
176 cmd = New SqlCommand("Select Inv_ID, RTRIM(InvoiceNo), InvoiceDate, ServiceID,RTRIM(ServiceCode),RTRIM(Customer.CustomerID),RTRIM(Name), RepairCharges, Upfront, ProductCharges, ServiceTaxPer, ServiceTax, GrandTotal, TotalPaid, Balance, RTRIM(InvoiceInfo1.Remarks) from Customer,Service,InvoiceInfo1 where Customer.ID=Service.CustomerID and Service.S_ID=InvoiceInfo1.ServiceID and InvoiceDate between @d1 and @d2 order by InvoiceDate", con)
177 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
178 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
179 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
180 dgw.Rows.Clear()
181 While (rdr.Read() = True)
182 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13), rdr(14), rdr(15))
183 End While
184 con.Close()
185 Catch ex As Exception
186 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
187 End Try
188 End Sub
189
190 Private Sub cmbOrderNo_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbInvoiceNo.SelectedIndexChanged
191 Try
192 con = New SqlConnection(cs)
193 con.Open()
194 cmd = New SqlCommand("Select Inv_ID, RTRIM(InvoiceNo), InvoiceDate, ServiceID,RTRIM(ServiceCode),RTRIM(Customer.CustomerID),RTRIM(Name), RepairCharges, Upfront, ProductCharges, ServiceTaxPer, ServiceTax, GrandTotal, TotalPaid, Balance, RTRIM(InvoiceInfo1.Remarks) from Customer,Service,InvoiceInfo1 where Customer.ID=Service.CustomerID and Service.S_ID=InvoiceInfo1.ServiceID and InvoiceNo='" & cmbInvoiceNo.Text & "' order by InvoiceDate", con)
195 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
196 dgw.Rows.Clear()
197 While (rdr.Read() = True)
198 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13), rdr(14), rdr(15))
199 End While
200 con.Close()
201 Catch ex As Exception
202 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
203 End Try
204 End Sub
205
206 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
207 Try
208 con = New SqlConnection(cs)
209 con.Open()
210 cmd = New SqlCommand("Select Inv_ID, RTRIM(InvoiceNo), InvoiceDate, ServiceID,RTRIM(ServiceCode),RTRIM(Customer.CustomerID),RTRIM(Name), RepairCharges, Upfront, ProductCharges, ServiceTaxPer, ServiceTax, GrandTotal, TotalPaid, Balance, RTRIM(InvoiceInfo1.Remarks) from Customer,Service,InvoiceInfo1 where Customer.ID=Service.CustomerID and Service.S_ID=InvoiceInfo1.ServiceID and InvoiceDate between @d1 and @d2 and Balance > 0 order by InvoiceDate", con)
211 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker2.Value.Date
212 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker1.Value.Date
213 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
214 dgw.Rows.Clear()
215 While (rdr.Read() = True)
216 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13), rdr(14), rdr(15))
217 End While
218 con.Close()
219 Catch ex As Exception
220 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
221 End Try
222 End Sub
223
224 Private Sub txtCustomerName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCustomerName.TextChanged
225 Try
226 con = New SqlConnection(cs)
227 con.Open()
228 cmd = New SqlCommand("Select Inv_ID, RTRIM(InvoiceNo), InvoiceDate, ServiceID,RTRIM(ServiceCode),RTRIM(Customer.CustomerID),RTRIM(Name), RepairCharges, Upfront, ProductCharges, ServiceTaxPer, ServiceTax, GrandTotal, TotalPaid, Balance, RTRIM(InvoiceInfo1.Remarks) from Customer,Service,InvoiceInfo1 where Customer.ID=Service.CustomerID and Service.S_ID=InvoiceInfo1.ServiceID and Name like '%" & txtCustomerName.Text & "%' order by InvoiceDate", con)
229 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
230 dgw.Rows.Clear()
231 While (rdr.Read() = True)
232 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13), rdr(14), rdr(15))
233 End While
234 con.Close()
235 Catch ex As Exception
236 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
237 End Try
238 End Sub
239
240 Private Sub cmbInvoiceNo_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbInvoiceNo.Format
241 If (e.DesiredType Is GetType(String)) Then
242 e.Value = e.Value.ToString.Trim
243 End If
244 End Sub
245 End Class
